ALTER    function fn_update_view_cost ( @product_id int, @year_of_txn int )
returns @fn_results Table(ProcessStep_ID bigint,		
			  PS_Type varchar(20),
			  Location_ID int,
			  Location varchar(80),
			  Q1_Standard_Cost float,
 			  Q1_Fixed_Over_head_Cost float,
			  Q1_Variable_Over_head_cost float,
			  Q1_Misc_Cost float,
			  Q1_Yield float,
			  Q2_Standard_Cost float,
			  Q2_Fixed_Over_head_Cost float,
			  Q2_Variable_Over_head_cost float,
			  Q2_Misc_Cost float,
		 	  Q2_Yield float,
			  Q3_Standard_Cost float,
			  Q3_Fixed_Over_head_Cost float,
			  Q3_Variable_Over_head_cost float,
			  Q3_Misc_Cost float,
		 	  Q3_Yield float,
			  Q4_Standard_Cost float,
			  Q4_Fixed_Over_head_Cost float,
			  Q4_Variable_Over_head_cost float,
			  Q4_Misc_Cost float,
			  Q4_Yield float
)
AS
begin
declare @results Table(ProcessStep_ID bigint,		
			  PS_Type varchar(20),
			  Location_ID int,
			  Location varchar(80),
			  Q1_Standard_Cost float,
 			  Q1_Fixed_Over_head_Cost float,
			  Q1_Variable_Over_head_cost float,
			  Q1_Misc_Cost float,
			  Q1_Yield float,
			  Q2_Standard_Cost float,
			  Q2_Fixed_Over_head_Cost float,
			  Q2_Variable_Over_head_cost float,
			  Q2_Misc_Cost float,
		 	  Q2_Yield float,
			  Q3_Standard_Cost float,
			  Q3_Fixed_Over_head_Cost float,
			  Q3_Variable_Over_head_cost float,
			  Q3_Misc_Cost float,
		 	  Q3_Yield float,
			  Q4_Standard_Cost float,
			  Q4_Fixed_Over_head_Cost float,
			  Q4_Variable_Over_head_cost float,
			  Q4_Misc_Cost float,
			  Q4_Yield float
)

	declare @flow_cursor CURSOR
	declare @location_cursor CURSOR
	declare @location_name varchar(80)
	declare @temp_ps_type varchar(20)
	declare @temp_ps_type_name varchar(20)
	declare @temp_process_step_id bigint
	declare @temp_process_step_id1 bigint
	declare @temp_location_id int

	declare @temp_Q1_Standard_Cost float
	declare @temp_Q1_Fixed_Over_head_Cost float
	declare @temp_Q1_Variable_Over_head_cost float
	declare @temp_Q1_Misc_Cost float
	declare @temp_Q1_Yield float
	declare @temp_Q2_Standard_Cost float
	declare @temp_Q2_Fixed_Over_head_Cost float
	declare @temp_Q2_Variable_Over_head_cost float
	declare @temp_Q2_Misc_Cost float
	declare @temp_Q2_Yield float
	declare @temp_Q3_Standard_Cost float
	declare @temp_Q3_Fixed_Over_head_Cost float
	declare @temp_Q3_Variable_Over_head_cost float
	declare @temp_Q3_Misc_Cost float
	declare @temp_Q3_Yield float
	declare @temp_Q4_Standard_Cost float
	declare @temp_Q4_Fixed_Over_head_Cost float
	declare @temp_Q4_Variable_Over_head_cost float
	declare @temp_Q4_Misc_Cost float
	declare @temp_Q4_Yield float
	declare @temp_Fin_Year datetime
	declare @rows_count int
	set @rows_count = -1


	set @temp_process_step_id = (select ProcessStep_ID from Product where Product_ID = @product_id)
	set @flow_cursor = CURSOR FOR
	select ProcessStep_ID,PS_Type,Process_Type_Name from fn_get_process_flow_by_processstep_id( @temp_process_step_id )
	open @flow_cursor
		fetch next from @flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
		while @@fetch_status = 0
		begin
			set @location_cursor = CURSOR FOR
			select ProcessStep_ID, Location_ID from Process_Step_Location_Table where  Processstep_Id = @temp_process_step_id
			open @location_cursor
				fetch next from @location_cursor into @temp_process_step_id1, @temp_location_id  
				while @@fetch_status = 0
				begin
					set @location_name = (select l.L_Vendor_Name+'/'+a.City from Location l, Address a where l.Location_ID = @temp_location_id and l.Address_ID = a.Address_ID)
					select @rows_count = count(processstep_id) from update_view_cost_table where processstep_id = @temp_process_step_id1 and location_id = @temp_location_id and year(fin_year) = @year_of_txn
					if @rows_count = 0
					begin
						if @temp_ps_type_name = 'FAB'
						begin
							insert into @results 
							(ProcessStep_ID, PS_Type, Location_ID, Location, 
							Q1_Standard_Cost, Q1_Fixed_Over_head_Cost, Q1_Variable_Over_head_cost, Q1_Misc_Cost, Q1_Yield, 
							Q2_Standard_Cost, Q2_Fixed_Over_head_Cost, Q2_Variable_Over_head_cost, Q2_Misc_Cost, Q2_Yield,
							Q3_Standard_Cost, Q3_Fixed_Over_head_Cost, Q3_Variable_Over_head_cost, Q3_Misc_Cost, Q3_Yield,
							Q4_Standard_Cost, Q4_Fixed_Over_head_Cost, Q4_Variable_Over_head_cost, Q4_Misc_Cost, Q4_Yield ) 
							values 
							(@temp_process_step_id1, @temp_ps_type_name, @temp_location_id, @location_name,
							0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 
							0.0, 0.0, 0.0, 0.0
							)
						end
						else
						begin
							insert into @results 
							(ProcessStep_ID, PS_Type, Location_ID, Location, 
							Q1_Standard_Cost, Q1_Fixed_Over_head_Cost, Q1_Variable_Over_head_cost, Q1_Misc_Cost, Q1_Yield, 
							Q2_Standard_Cost, Q2_Fixed_Over_head_Cost, Q2_Variable_Over_head_cost, Q2_Misc_Cost, Q2_Yield,
							Q3_Standard_Cost, Q3_Fixed_Over_head_Cost, Q3_Variable_Over_head_cost, Q3_Misc_Cost, Q3_Yield,
							Q4_Standard_Cost, Q4_Fixed_Over_head_Cost, Q4_Variable_Over_head_cost, Q4_Misc_Cost, Q4_Yield ) 
							values 
							(@temp_process_step_id1, @temp_ps_type, @temp_location_id, @location_name,
							0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 
							0.0, 0.0, 0.0, 0.0
							)
						end
					end
					else if @rows_count > 0
					begin
						SELECT 
						@temp_Q1_Standard_Cost = Q1_Standard_Cost, @temp_Q1_Fixed_Over_head_Cost = Q1_Fixed_Over_head_Cost, @temp_Q1_Variable_Over_head_cost = Q1_Variable_Over_head_cost, @temp_Q1_Misc_Cost = Q1_Misc_Cost, @temp_Q1_Yield = Q1_Yield, 
						@temp_Q2_Standard_Cost = Q2_Standard_Cost, @temp_Q2_Fixed_Over_head_Cost = Q2_Fixed_Over_head_Cost, @temp_Q2_Variable_Over_head_cost = Q2_Variable_Over_head_cost, @temp_Q2_Misc_Cost = Q2_Misc_Cost, @temp_Q2_Yield = Q2_Yield, 
						@temp_Q3_Standard_Cost = Q3_Standard_Cost, @temp_Q3_Fixed_Over_head_Cost = Q3_Fixed_Over_head_Cost, @temp_Q3_Variable_Over_head_cost = Q3_Variable_Over_head_cost, @temp_Q3_Misc_Cost = Q3_Misc_Cost, @temp_Q3_Yield = Q3_Yield, 
						@temp_Q4_Standard_Cost = Q4_Standard_Cost, @temp_Q4_Fixed_Over_head_Cost = Q4_Fixed_Over_head_Cost, @temp_Q4_Variable_Over_head_cost = Q4_Variable_Over_head_cost, @temp_Q4_Misc_Cost = Q4_Misc_Cost, @temp_Q4_Yield = Q4_Yield, 
						@temp_Fin_Year = Fin_Year FROM Update_View_Cost_Table 
						where ProcessStep_ID = @temp_process_step_id1 and Location_id = @temp_location_id and year(fin_year) = @year_of_txn

						if @temp_ps_type_name = 'FAB'
						begin
							insert into @results 
							(ProcessStep_ID, PS_Type, Location_ID, Location, 
							Q1_Standard_Cost, Q1_Fixed_Over_head_Cost, Q1_Variable_Over_head_cost, Q1_Misc_Cost, Q1_Yield, 
							Q2_Standard_Cost, Q2_Fixed_Over_head_Cost, Q2_Variable_Over_head_cost, Q2_Misc_Cost, Q2_Yield,
							Q3_Standard_Cost, Q3_Fixed_Over_head_Cost, Q3_Variable_Over_head_cost, Q3_Misc_Cost, Q3_Yield,
							Q4_Standard_Cost, Q4_Fixed_Over_head_Cost, Q4_Variable_Over_head_cost, Q4_Misc_Cost, Q4_Yield ) 
							values 
							(@temp_process_step_id1, @temp_ps_type_name, @temp_location_id, @location_name,
							@temp_Q1_Standard_Cost, @temp_Q1_Fixed_Over_head_Cost, @temp_Q1_Variable_Over_head_cost, @temp_Q1_Misc_Cost, @temp_Q1_Yield, 
							@temp_Q2_Standard_Cost, @temp_Q2_Fixed_Over_head_Cost, @temp_Q2_Variable_Over_head_cost, @temp_Q2_Misc_Cost, @temp_Q2_Yield, 
							@temp_Q3_Standard_Cost, @temp_Q3_Fixed_Over_head_Cost, @temp_Q3_Variable_Over_head_cost, @temp_Q3_Misc_Cost, @temp_Q3_Yield, 
							@temp_Q4_Standard_Cost, @temp_Q4_Fixed_Over_head_Cost, @temp_Q4_Variable_Over_head_cost, @temp_Q4_Misc_Cost, @temp_Q4_Yield
							)
						end
						else
						begin
							insert into @results 
							(ProcessStep_ID, PS_Type, Location_ID, Location, 
							Q1_Standard_Cost, Q1_Fixed_Over_head_Cost, Q1_Variable_Over_head_cost, Q1_Misc_Cost, Q1_Yield, 
							Q2_Standard_Cost, Q2_Fixed_Over_head_Cost, Q2_Variable_Over_head_cost, Q2_Misc_Cost, Q2_Yield,
							Q3_Standard_Cost, Q3_Fixed_Over_head_Cost, Q3_Variable_Over_head_cost, Q3_Misc_Cost, Q3_Yield,
							Q4_Standard_Cost, Q4_Fixed_Over_head_Cost, Q4_Variable_Over_head_cost, Q4_Misc_Cost, Q4_Yield ) 
							values 
							(@temp_process_step_id, @temp_ps_type, @temp_location_id, @location_name,
							@temp_Q1_Standard_Cost, @temp_Q1_Fixed_Over_head_Cost, @temp_Q1_Variable_Over_head_cost, @temp_Q1_Misc_Cost, @temp_Q1_Yield, 
							@temp_Q2_Standard_Cost, @temp_Q2_Fixed_Over_head_Cost, @temp_Q2_Variable_Over_head_cost, @temp_Q2_Misc_Cost, @temp_Q2_Yield, 
							@temp_Q3_Standard_Cost, @temp_Q3_Fixed_Over_head_Cost, @temp_Q3_Variable_Over_head_cost, @temp_Q3_Misc_Cost, @temp_Q3_Yield, 
							@temp_Q4_Standard_Cost, @temp_Q4_Fixed_Over_head_Cost, @temp_Q4_Variable_Over_head_cost, @temp_Q4_Misc_Cost, @temp_Q4_Yield
							)
						end
					end
					fetch next from @location_cursor into @temp_process_step_id1, @temp_location_id  
				end
				close @location_cursor
				deallocate @location_cursor
			fetch next from @flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
		end
	close @flow_cursor
	deallocate @flow_cursor

	insert @fn_results select *from @results
	return
end